用拉链表实现核心交易分析中DIM层商家维表，并实现该拉链表的回滚

 1.创建表加载数据(准备工作)
   1).创建商家信息表--拉链表 (dim层)
   特点：
       相较于商家维表，多了两个字段startdate,enddate
       拉链表不是分区表
   
   DROP TABLE IF EXISTS `dim.dim_trade_shops`;
CREATE EXTERNAL TABLE `dim.dim_trade_shops`(
`shopid` int,
`userid` int,
`areaid` int,
`shopname` string,
`shoplevel` tinyint,
`status` tinyint,
`createtime` string,
`modifytime` string,
`startdate` string,
`enddate` string)
COMMENT '商家店铺表';
    
   2).准备三天的数据
   
   /data/lagoudw/data/shop-2020-11-20.dat
100050,1,100225,WSxxx营超市,1,1,2020-06-28,2020-11-20 13:22:22
100052,2,100236,新鲜xxx旗舰店,1,1,2020-06-28,2020-11-20 13:22:22
100053,3,100011,华为xxx旗舰店,1,1,2020-06-28,2020-11-20 13:22:22
100054,4,100159,小米xxx旗舰店,1,1,2020-06-28,2020-11-20 13:22:22
100055,5,100211,苹果xxx旗舰店,1,1,2020-06-28,2020-11-20 13:22:22

   /data/lagoudw/data/shop-2020-11-21.dat
100057,7,100311,三只xxx鼠零食,1,1,2020-06-28,2020-11-21 13:22:22
100058,8,100329,良子xxx铺美食,1,1,2020-06-28,2020-11-21 13:22:22
100054,4,100159,小米xxx旗舰店,2,1,2020-06-28,2020-11-21 13:22:22
100055,5,100211,苹果xxx旗舰店,2,1,2020-06-28,2020-11-21 13:22:22
  
  /data/lagoudw/data/shop-2020-11-22.dat
100059,9,100225,乐居xxx日用品,1,1,2020-06-28,2020-11-22 13:22:22
100060,10,100211,同仁xxx大健康,1,1,2020-06-28,2020-11-22 13:22:22
100052,2,100236,新鲜xxx旗舰店,1,2,2020-06-28,2020-11-22 13:22:22
  
  3).创建商家信息维表(ods层)
  DROP TABLE IF EXISTS `ods.ods_trade_shops`;
CREATE EXTERNAL TABLE `ods.ods_trade_shops`(
`shopid` int,
`userid` int,
`areaid` int,
`shopname` string,
`shoplevel` tinyint,
`status` tinyint,
`createtime` string,
`modifytime` string)
COMMENT '商家店铺表'
PARTITIONED BY (`dt` string)
row format delimited fields terminated by ','
location '/user/data/trade.db/shops';
  
 2.拉链表的实现
   ods_trade_shops(分区表)=> 每日变更的数据(修改的+新增的)/历史数据(第一天)
   dim_trade_shops（拉链表）=> 多了两个字段 start_date / end_date
   1).ods_trade_shops初始化（2020-11-20)。获取历史数据
      load data local inpath '/data/lagoudw/data/shop-2020-11-20.dat' into 
table ods.ods_trade_shops partition(dt = '2020-11-20');

100050,1,100225,WSxxx营超市,1,1,2020-06-28,2020-11-20 13:22:22
100052,2,100236,新鲜xxx旗舰店,1,1,2020-06-28,2020-11-20 13:22:22
100053,3,100011,华为xxx旗舰店,1,1,2020-06-28,2020-11-20 13:22:22
100054,4,100159,小米xxx旗舰店,1,1,2020-06-28,2020-11-20 13:22:22
100055,5,100211,苹果xxx旗舰店,1,1,2020-06-28,2020-11-20 13:22:22
   
   2).初始化拉链表(2020-11-20)。ods_trade_shops => dim_trade_shops
   insert overwrite table dim.dim_trade_shops
select shopid,userid,areaid,shopname,shoplevel,status,createtime,modifytime, 
dt as start_date, 
'9999-12-31' as end_date
from ods.ods_trade_shops
where dt='2020-11-20';
   3).次日新增数据（2020-11-21）；获取新增数据
   load data local inpath '/data/lagoudw/data/shop-2020-11-21.dat' into 
table ods.ods_trade_shops partition(dt = '2020-11-21');
100057,7,100311,三只xxx鼠零食,1,1,2020-06-28,2020-11-21 13:22:22
100058,8,100329,良子xxx铺美食,1,1,2020-06-28,2020-11-21 13:22:22
100054,4,100159,小米xxx旗舰店,2,1,2020-06-28,2020-11-21 13:22:22
100055,5,100211,苹果xxx旗舰店,2,1,2020-06-28,2020-11-21 13:22:22
   4).构建拉链表（新增数据 + 历史数据）
   新增数据处理逻辑与家在历史数据相似，startdate=dt enddate = '9999-12-31'
   (1).新增数据的处理方式
   select shopid,userid,areaid,shopname,shoplevel,status,createtime,modifytime, 
dt as start_date, 
'9999-12-31' as enddate
from ods.ods_trade_shops
where dt='2020-11-21';
   (2).历史数据的处理的方式
   select B.shopid,B.userid,B.areaid,B.shopname,B.shoplevel,
B.status,B.createtime,B.modifytime,B.startdate,
case when B.enddate='9999-12-31' and A.shopid is not null
then date_add('2020-06-21', -1)
else B.enddate
end as end_date 
from (select * from ods.ods_trade_shops where dt='2020-11-21') A
right join dim.dim_trade_shops B
on A.shopid=B.shopid;
   
    最终的处理（新增+历史数据）
    insert overwrite table dim.dim_trade_shops
select shopid,userid,areaid,shopname,shoplevel,status,createtime,modifytime, 
dt as start_date, 
'9999-12-31' as enddate
from ods.ods_trade_shops
where dt='2020-11-21';

union all
	
select B.shopid,B.userid,B.areaid,B.shopname,B.shoplevel,
B.status,B.createtime,B.modifytime,B.startdate,
case when B.enddate='9999-12-31' and A.shopid is not null
then date_add('2020-11-21', -1)
else B.enddate
end as enddate 
from (select * from ods.ods_trade_shops where dt='2020-11-21') A
right join dim.dim_trade_shops B
on A.shopid=B.shopid;
   
   处理拉链表的脚本(测试脚本)：
    /data/lagoudw/data/shopszipper.sh

#!/bin/bash

source /etc/profile

if [ -n "$1" ] ;then
do_date=$1
else
do_date=`date -d "-1 day" +%F`
fi

sql="
insert overwrite table dim.dim_trade_shops
select shopid,userid,areaid,shopname,shoplevel,status,createtime,modifytime, 
dt as start_date, 
'9999-12-31' as enddate
from ods.ods_trade_shops
where dt='$do_date'

union all
	
select B.shopid,B.userid,B.areaid,B.shopname,B.shoplevel,
B.status,B.createtime,B.modifytime,B.startdate,
case when B.enddate='9999-12-31' and A.shopid is not null
then date_add('$do_date', -1)
else B.enddate
end as enddate 
from (select * from ods.ods_trade_shops where dt='$do_date') A
right join dim.dim_trade_shops B
on A.shopid=B.shopid;
"

hive -e "$sql"
 
 3.拉链的回滚
   
   由于种种原因需要将拉链表恢复到 rollback_date 那一天的数据。此时有：
     end_date < rollback_date，即结束日期 < 回滚日期。表示该行数据在
rollback_date 之前产生，这些数据需要原样保留
     start_date <= rollback_date <= end_date，即开始日期 <= 回滚日期 <= 结束日
期。这些数据是回滚日期之后产生的，但是需要修改。将enddate 改为 9999-12-31
     其他数据不用管
   1)处理 enddate < rollback_date 的数据，保留
   select shopid,userid,areaid,shopname,shoplevel,status,createtime,modifytime, 
startdate, enddate, '1' as tag
from dim.dim_trade_shops
where enddate < '2020-11-21';
   2).处理 start_date <= rollback_date <= end_date 的数据，设置 end_date=9999-12-31
   select shopid,userid,areaid,shopname,shoplevel,status,createtime,modifytime,
startdate, enddate, '2' as tag
from dim.dim_trade_shops
where startdate <= '2020-11-21' and enddate >= '2020-11-21';
   3).将前面两步的数据写入临时表tmp（拉链表）
   drop table test.tmp2;
create table test.tmp2 as
select shopid,userid,areaid,shopname,shoplevel,status,createtime,modifytime, 
startdate, enddate, '1' as tag
from dim.dim_trade_shops
where enddate < '2020-11-21';

union all

select shopid,userid,areaid,shopname,shoplevel,status,createtime,modifytime,
startdate, enddate, '2' as tag
from dim.dim_trade_shops
where startdate <= '2020-11-21' and enddate >= '2020-11-21';

  select * from test.tmp2 cluster by shopid, startdate;
	 
   4)模拟脚本
   /data/lagoudw/data/shopstmp.sh

#!/bin/bash

source /etc/profile

if [ -n "$1" ] ;then
do_date=$1
else
do_date=`date -d "-1 day" +%F`
fi

sql="
drop table test.tmp2;
create table test.tmp2 as
select shopid,userid,areaid,shopname,shoplevel,status,createtime,modifytime, 
startdate, enddate, '1' as tag
from dim.dim_trade_shops
where enddate < '$do_date'

union all

select shopid,userid,areaid,shopname,shoplevel,status,createtime,modifytime,
startdate, enddate, '2' as tag
from dim.dim_trade_shops
where startdate <= '$do_date' and enddate >= '$do_date';
"

hive -e "$sql"
   
  逐天回滚，检查数据；


   